package com.yellowpage.ndc.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.LinkedMap;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.yellowpage.white.common.Constants;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;

import com.yellowpage.ndc.data.ProdGroup;
import com.yellowpage.ndc.dao.ProdGroupDao;

public class ProdGroupServiceImpl extends BaseServiceImpl implements ProdGroupService {
	  private ProdGroupDao prodGroupDao;
	
	  public void setProdGroupDao(ProdGroupDao prodGroupDao) {
		  this.prodGroupDao = prodGroupDao;
	  }
	
	
	  /* Smooth generate Method */ 
	  public ProdGroup getProdGroup(Long id){
	      return prodGroupDao.get(id);
	  }

		  public List<Map> getProdGrouplistorder(Integer listOrder){
		  QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select a.ID from db_ndc.PROD_GROUP a  where a.LIST_ORDER = '" + listOrder + "'"); 	
	         
			List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), new HashMap());
			return resultList;
	  }
	   
    public List<ProdGroup> getAllProdGroup(){
        return prodGroupDao.getAll();
    }
    
    public List<Map> queryProdGroupByPage(Map queryMap, PageRender pageRender) {		
        
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				" SELECT a.PROD_CLASS_CODE1 , a.PROD_CLASS_NAME1 , a.PROD_CLASS_CODE2 , a.PROD_CLASS_NAME2 , " +
				" a.GROUP_ID , a.NAME1 , a.NAME2 , a.SUBNAME1 , a.SUBNAME2 , a.SUBNAME3 , a.SUBNAME4 , " +
				" a.AREA_CODE , a.TEL, a.TEL2 , a.TEL_EXT , a.FAX_FLAG , a.SB , a.LIST_ORDER , a.REGION_CODE , a.REGION_NAME , a.CITY_CODE , a.CITY_NAME , " +
				" a.DESTRICT_CODE , a.DESTRICT_NAME  , a.ADDR , a.ZIP , a.PY_ADDR , a.ENG_ADDR , a.POIX , a.POIY , " +
				" a.CUST_STATUS , a.CUST_TYPE_CODE , a.CLASS_MAIN_ID , a.ORG_ID , a.URL , a.WEIBO , a.WEIXIN , a.YIXIN , a.LOGO , " +
				" a.SAFE_STATUS , a.BX_NAME , a.PY_NAME , a.ENG_NAME , a.SP_NAME , a.PROD_CLASS_ID , " +
				" date_format(a.MODIFY_TIME,'%Y-%m-%d') as MODIFY_TIME , a.MODIFY_EMP_ID , " +
				" date_format(a.INPUT_TIME,'%Y-%m-%d') as INPUT_TIME , a.INPUT_EMP_ID , a.ID,    " +
				" (select display_name from CA_USER where user_id = a.MODIFY_EMP_ID) as MODIFY_EMP_CODE, " +
				" (select display_name from CA_USER where user_id = a.INPUT_EMP_ID) as INPUT_EMP_CODE " +
				" FROM db_ndc.PROD_GROUP a " +
				" WHERE 1 = 1 ")
//				+ " and PROD_CLASS_CODE1 LIKE 'K5%' ")
				    			.addCondition(" and PROD_CLASS_CODE1 LIKE :prodClassCode1 ")
							    .addCondition(" and PROD_CLASS_NAME1 LIKE :prodClassName1 ")
							    .addCondition(" and PROD_CLASS_CODE2 LIKE :prodClassCode2 ")
							    .addCondition(" and PROD_CLASS_NAME2 LIKE :prodClassName2 ")
							    .addCondition(" and GROUP_ID LIKE :groupId ")
							    .addCondition(" and NAME1 LIKE :name1 ")
							    .addCondition(" and NAME2 LIKE :name2 ")
							    .addCondition(" and TEL LIKE :tel ")
							    .addCondition(" and AREA_CODE LIKE :areaCode ")
							    .addCondition(" and REGION_CODE LIKE :regionCode ")
							    .addCondition(" and REGION_NAME LIKE :regionName ")
							    .addCondition(" and CITY_CODE LIKE :cityCode ")
							    .addCondition(" and CITY_NAME LIKE :cityName ")
							    .addCondition(" and PY_NAME LIKE :pyName ")
							    .addCondition(" and SP_NAME LIKE :spName ")
							    .addCondition(" and date_format(INPUT_TIME,'%Y-%m-%d') =  :inputTime  ")
							    .addCondition(" and date_format(MODIFY_TIME,'%Y-%m-%d') =  :modifyTime  ")
							    .addCondition(" and MODIFY_EMP_ID = (select USER_ID from CA_USER where DISPLAY_NAME = :modifyEmpCode ) ")
							    .addCondition(" and INPUT_EMP_ID = (select USER_ID from CA_USER where DISPLAY_NAME = :inputEmpCode ) ");    
		    

		List<Map> resultList = sqlAssistant.queryByPage(baseDao.getConnection(), queryMap, pageRender, true);

		return resultList;
	}
    
public List<Map> ProdGroupexcel(Map queryMap) {		
        
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				" SELECT a.PROD_CLASS_CODE1 , a.PROD_CLASS_NAME1 , a.PROD_CLASS_CODE2 , a.PROD_CLASS_NAME2 , " +
				" a.GROUP_ID , a.NAME1 , a.NAME2 , a.SUBNAME1 , a.SUBNAME2 , a.SUBNAME3 , a.SUBNAME4 , " +
				" a.AREA_CODE , a.TEL ,  a.TEL2 , a.TEL3 , a.TEL4 ,a.TEL_EXT , a.FAX_FLAG , a.SB , a.LIST_ORDER , a.REGION_CODE , a.REGION_NAME , a.CITY_CODE , a.CITY_NAME , " +
				" a.DESTRICT_CODE , a.DESTRICT_NAME , a.ADDR , a.ZIP , a.PY_ADDR , a.ENG_ADDR , a.POIX , a.POIY , " +
				" a.CUST_STATUS , a.CUST_TYPE_CODE , a.CLASS_MAIN_ID , a.ORG_ID , a.URL , a.WEIBO , a.WEIXIN , a.YIXIN , a.LOGO , " +
				" a.SAFE_STATUS , a.BX_NAME , a.PY_NAME , a.ENG_NAME , a.SP_NAME , a.PROD_CLASS_ID , " +
				" date_format(a.MODIFY_TIME,'%Y-%m-%d') as MODIFY_TIME , a.MODIFY_EMP_ID , " +
				" date_format(a.INPUT_TIME,'%Y-%m-%d') as INPUT_TIME , a.INPUT_EMP_ID , a.ID,    " +
				" (select display_name from CA_USER where user_id = a.MODIFY_EMP_ID) as MODIFY_EMP_CODE, " +
				" (select display_name from CA_USER where user_id = a.INPUT_EMP_ID) as INPUT_EMP_CODE " +
				" FROM db_ndc.PROD_GROUP a " +
				" WHERE 1 = 1 ")
//				+ " and PROD_CLASS_CODE1 LIKE 'K5%' ")
				    			.addCondition(" and PROD_CLASS_CODE1 LIKE :prodClassCode1 ")
							    .addCondition(" and PROD_CLASS_NAME1 LIKE :prodClassName1 ")
							    .addCondition(" and PROD_CLASS_CODE2 LIKE :prodClassCode2 ")
							    .addCondition(" and PROD_CLASS_NAME2 LIKE :prodClassName2 ")
							    .addCondition(" and GROUP_ID LIKE :groupId ")
							    .addCondition(" and NAME1 LIKE :name1 ")
							    .addCondition(" and NAME2 LIKE :name2 ")
							    .addCondition(" and TEL LIKE :tel ")
							    .addCondition(" and AREA_CODE LIKE :areaCode ")
							    .addCondition(" and REGION_CODE LIKE :regionCode ")
							    .addCondition(" and REGION_NAME LIKE :regionName ")
							    .addCondition(" and CITY_CODE LIKE :cityCode ")
							    .addCondition(" and CITY_NAME LIKE :cityName ")
							    .addCondition(" and PY_NAME LIKE :pyName ")
							    .addCondition(" and SP_NAME LIKE :spName ")
							    .addCondition(" and date_format(INPUT_TIME,'%Y-%m-%d') =  :inputTime  ")
							    .addCondition(" and date_format(MODIFY_TIME,'%Y-%m-%d') =  :modifyTime  ")
							    .addCondition(" and MODIFY_EMP_ID = (select USER_ID from CA_USER where DISPLAY_NAME = :modifyEmpCode ) ")
							    .addCondition(" and INPUT_EMP_ID = (select USER_ID from CA_USER where DISPLAY_NAME = :inputEmpCode ) ");    
		    

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), queryMap);

		return resultList;
	}
    
public List<Map> ProdGroupOrderByPage(Map queryMap, PageRender pageRender) {		
        
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				" SELECT a.PROD_CLASS_CODE1 , a.PROD_CLASS_NAME1 , a.PROD_CLASS_CODE2 , a.PROD_CLASS_NAME2 , " +
				" a.GROUP_ID , a.NAME1 , a.NAME2 , a.SUBNAME1 , a.SUBNAME2 , a.SUBNAME3 , a.SUBNAME4 , " +
				" a.AREA_CODE , a.TEL , a.TEL_EXT , a.FAX_FLAG , a.SB , a.LIST_ORDER , a.REGION_CODE , a.REGION_NAME , a.CITY_CODE , a.CITY_NAME , " +
				" a.DESTRICT_CODE , a.DESTRICT_NAME  , a.ADDR , a.ZIP , a.PY_ADDR , a.ENG_ADDR , a.POIX , a.POIY , " +
				" a.CUST_STATUS , a.CUST_TYPE_CODE , a.CLASS_MAIN_ID , a.ORG_ID , a.URL , a.WEIBO , a.WEIXIN , a.YIXIN , a.LOGO , " +
				" a.SAFE_STATUS , a.BX_NAME , a.PY_NAME , a.ENG_NAME , a.SP_NAME , a.PROD_CLASS_ID , " +
				" date_format(a.MODIFY_TIME,'%Y-%m-%d') as MODIFY_TIME , a.MODIFY_EMP_ID , " +
				" date_format(a.INPUT_TIME,'%Y-%m-%d') as INPUT_TIME , a.INPUT_EMP_ID , a.ID,    " +
				" (select display_name from CA_USER where user_id = a.MODIFY_EMP_ID) as MODIFY_EMP_CODE, " +
				" (select display_name from CA_USER where user_id = a.INPUT_EMP_ID) as INPUT_EMP_CODE " +
				" FROM db_ndc.PROD_GROUP a " +
				" WHERE 1 = 1 ")
//				+ " and PROD_CLASS_CODE1 LIKE 'K5%' ")
				    			.addCondition(" and PROD_CLASS_CODE1 LIKE :prodClassCode1 ")
							    .addCondition(" and PROD_CLASS_NAME1 LIKE :prodClassName1 ")
							    .addCondition(" and PROD_CLASS_CODE2 LIKE :prodClassCode2 ")
							    .addCondition(" and PROD_CLASS_NAME2 LIKE :prodClassName2 ")
							    .addCondition(" and GROUP_ID LIKE :groupId ")
							    .addCondition(" and NAME1 LIKE :name1 ")
							    .addCondition(" and NAME2 LIKE :name2 ")
							    .addCondition(" and TEL LIKE :tel ")
							    .addCondition(" and AREA_CODE LIKE :areaCode ")
							    .addCondition(" and REGION_CODE LIKE :regionCode ")
							    .addCondition(" and REGION_NAME LIKE :regionName ")
							    .addCondition(" and CITY_CODE LIKE :cityCode ")
							    .addCondition(" and CITY_NAME LIKE :cityName ")
							    .addCondition(" and PY_NAME LIKE :pyName ")
							    .addCondition(" and SP_NAME LIKE :spName ")
							    .addCondition(" and date_format(INPUT_TIME,'%Y-%m-%d') =  :inputTime  ")
							    .addCondition(" and date_format(MODIFY_TIME,'%Y-%m-%d') =  :modifyTime  ")
							    .addCondition(" and MODIFY_EMP_ID = (select USER_ID from CA_USER where DISPLAY_NAME = :modifyEmpCode ) ")
							    .addCondition(" and INPUT_EMP_ID = (select USER_ID from CA_USER where DISPLAY_NAME = :inputEmpCode ) ");    
		    
		sqlAssistant.setTail(" order by LIST_ORDER ");
		List<Map> resultList = sqlAssistant.queryByPage(baseDao.getConnection(), queryMap, pageRender, true);

		return resultList;
	}

public List<Map> ProdGroupOrder(Map queryMap) {		
    
	QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
/*			" select substring(a.PROD_CLASS_CODE1,1,2) AS L1,substring(a.PROD_CLASS_CODE1,3,2) AS L2," +
			"substring(a.PROD_CLASS_CODE1,5,2) AS L3 " +
			" FROM db_ndc.PROD_GROUP a " +
			" WHERE 1 = 1 ")*/
						" select a.CLASS_ID,a.CODE,a.NAME"+
			" FROM db_ndc.PROD_CLASS a " +
			" WHERE 1 = 1 ")
//			+ " and PROD_CLASS_CODE1 LIKE 'K5%' ")
			    			.addCondition(" and CODE LIKE :prodClassCode1 ")
						   ;    
	    
	
	List<Map> resultList = sqlAssistant.query(
			baseDao.getConnection(), queryMap);

	return resultList;
}
    
	public String getRegionByCode(String regionCode){
		log.error("getRegionByCode in...................");
    	String source = "";
    	
    	QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select NAME from db_ndc.MANAGE_REGION mr where mr.REGION_CODE = '" + regionCode + "'"); 	
         
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), new HashMap());
						
		for(int i = 0; i < resultList.size(); i++){
			source = resultList.get(i).get("NAME").toString();
		}
		
		return source;
    }
	
	public String getGroupId(){
		log.error("getGroupId in...................");
    	String source = "";
    	
    	QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select max(right(pg.GROUP_ID,8)) as MaxSeq from db_ndc.PROD_GROUP pg where pg.GROUP_ID LIKE 'GFOF%' "); 	
         
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), new HashMap());

		for(int i = 0; i < resultList.size(); i++){
			source = resultList.get(i).get("MAXSEQ").toString();
		}
//		log.error("source is " + source);
		return source;
    }
	


	 public List<Map> countProdGroup(Map queryMap) {		
	        
			QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
					"SELECT COUNT(*) AS 'TOTAL'  FROM db_ndc.PROD_GROUP  where 1=1 ")
					    		.addCondition(" and PROD_CLASS_CODE1 LIKE :prodClassCode1 ")
							    .addCondition(" and PROD_CLASS_NAME1 LIKE :prodClassName1 ")
							    .addCondition(" and PROD_CLASS_CODE2 LIKE :prodClassCode2 ")
							    .addCondition(" and PROD_CLASS_NAME2 LIKE :prodClassName2 ")
							    .addCondition(" and GROUP_ID LIKE :groupId ")
							    .addCondition(" and NAME1 LIKE :name1 ")
							    .addCondition(" and NAME2 LIKE :name2 ")
							    .addCondition(" and TEL LIKE :tel ")
							    .addCondition(" and AREA_CODE LIKE :areaCode ")
							    .addCondition(" and REGION_CODE LIKE :regionCode ")
							    .addCondition(" and REGION_NAME LIKE :regionName ")
							    .addCondition(" and CITY_CODE LIKE :cityCode ")
							    .addCondition(" and CITY_NAME LIKE :cityName ")
							    .addCondition(" and PY_NAME LIKE :pyName ")
							    .addCondition(" and SP_NAME LIKE :spName ")
							    .addCondition(" and date_format(INPUT_TIME,'%Y-%m-%d') =  :inputTime  ")
							    .addCondition(" and date_format(MODIFY_TIME,'%Y-%m-%d') =  :modifyTime  ")
							    .addCondition(" and MODIFY_EMP_ID = (select USER_ID from CA_USER where DISPLAY_NAME = :modifyEmpCode ) ")
							    .addCondition(" and INPUT_EMP_ID = (select USER_ID from CA_USER where DISPLAY_NAME = :inputEmpCode ) ");    
								    	    
			
			List<Map> resultList = sqlAssistant.query(
					baseDao.getConnection(), queryMap);

			return resultList;
		}
	
	 
	 
	public void saveProdGroup(ProdGroup prodGroup) {
		prodGroupDao.save(prodGroup);
	}

	public void updateProdGroup(ProdGroup prodGroup) {
		prodGroupDao.update(prodGroup);
	}

	public void delete(List deleteList) {
		prodGroupDao.remove(deleteList);
	}
	
	public List<Map> getIdByGroupId(String groupId){
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select ID from db_ndc.PROD_GROUP where GROUP_ID ='"+groupId+"' "); 	
        
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), new HashMap());
		
		return resultList;
	}
	  
	  
	 
	
}	